package net.ghy.statistics;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.springframework.context.ApplicationContext;

import dao.hb.BxMidle;


import net.bat.db.BaseDAO;
import net.bat.db.BaseDAOImpl;
import net.ghy.statistics.ages.AgesExpData;
import net.ghy.statistics.department.DepExpData;
import net.ghy.statistics.other.OtherData;
import net.ghy.statistics.utils.EUtils;


public class ExportExcel {
	static private BaseDAO dao;
	private static ApplicationContext ctx = null;
	private static final String DATE_FORMAT = "yyyy-MM-dd-hh-mm-ss";
	private String realPath;
	private String tempPath;
	
	public String getRealPath() {
		return realPath;
	}

	public void setRealPath(String realPath) {
		this.realPath = realPath;
	} 
	
	public String getTempPath() {
		return tempPath;
	}

	public void setTempPath(String tempPath) {
		this.tempPath = tempPath;
	}
	

	public BaseDAO getDao() {
		return dao;
	}

	public void setDao(BaseDAO dao) {
		this.dao = dao;
	}
	
	private String destFile;
	
	public File buildDestPath() {
		SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		File fd = null;  
        try {  
            fd = new File(tempPath);  
            if (!fd.exists()) {  
                fd.mkdirs();  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            fd = null;  
        } 
        destFile = tempPath + File.separator + sdf.format(Calendar.getInstance().getTime()).toString() + "_QY.xls" ;
		return new File(destFile);
	}
	
    
public void exportExcel(OutputStream out) {
	BufferedOutputStream  dataOut=null;
	try {
        File f0 = new File(destFile);
        byte[] b0 = Byte_File_Object.getBytesFromFile(f0);
        dataOut = new BufferedOutputStream(out);  
        dataOut.write(b0,0,b0.length); 
    	if (dataOut!=null) {
            dataOut.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}
    public void ExportQY(File file,String start, String end) throws Exception {
		Map<String, List> zztj = EUtils.getStateTotaltj("在职",start,end);
		DepExpData ded = new DepExpData();
		List dep = EUtils.getDepList(start,end);
		Map<String, List> dptUser = ded.userbxData(dep,start,end);// {人事处={付春娟=[{门诊=dao.hb.BxMidle@12ecba8}, {住院=dao.hb.BxMidle@13c8540}], 王纯=[{门诊=dao.hb.BxMidle@504f3c},	
		if(!start.contains("01-01")){
			ExcelStyle es = new ExcelStyle(file,"全院统计");
			es.setStyleToExcel("Q",start,end);
	        Iterator it = dptUser.keySet().iterator(); 
	        int colstart=3,rowstart=3;
			while(it.hasNext()){ 
				String key = it.next().toString(); 
				if(!"退休".equals(key)){
					HashMap ls = (HashMap) dptUser.get(key);
					int rowsnum = es.addExcel(ls,colstart,rowstart);
					es.ws.addCell(new Label(1, rowstart, key));
					es.ws.mergeCells(1,rowstart, 1, rowsnum+rowstart-1);
					rowstart = rowstart+rowsnum;
				}
			}
			es.addExcel(zztj,colstart,rowstart);
			//es.ws.mergeCells(2,rowstart+1, 1, rowstart+1);
			rowstart+=1;
			HashMap value = (HashMap) dptUser.get("退休"); 
			es.ws.addCell(new Label(0, rowstart, "退休"));
			int rowsnum = es.addExcel(value,colstart,rowstart);
			es.ws.mergeCells(0,rowstart, 1, rowsnum+rowstart-1);
			try {
				es.wwb.write();
				es.wwb.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else
			ExportQYBgf(file,dptUser,start,end);

	}
   
    public void ExportQYBgf(File file,Map<String, List> dptUser,String start, String end) throws RowsExceededException, WriteException{
		Map<String, List> zztj = null;
		try {
			zztj = EUtils.getStateTotaltjBaogf("在职",start,end);
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List dep = EUtils.getDepList(start,end);
    	ExcelStyle es = new ExcelStyle(file,"全院统计");
		es.setStyleToExcel("B",start,end);
        Iterator it = dptUser.keySet().iterator(); 
        int colstart=3,rowstart=3;
		while(it.hasNext()){ 
			String key = it.next().toString(); 
			if(!"退休".equals(key)){
				HashMap ls = (HashMap) dptUser.get(key);
				int rowsnum = es.addExcel(ls,colstart,rowstart);
				es.ws.addCell(new Label(1, rowstart, key));
				es.ws.mergeCells(1,rowstart, 1, rowsnum+rowstart-1);
				rowstart = rowstart+rowsnum;
			}
		}
		es.addExcel(zztj,colstart,rowstart);
		//es.ws.mergeCells(2,rowstart+1, 1, rowstart+1);
		rowstart+=1;
		HashMap value = (HashMap) dptUser.get("退休"); 
		es.ws.addCell(new Label(0, rowstart, "退休"));
		int rowsnum = es.addExcel(value,colstart,rowstart);
		es.ws.mergeCells(0,rowstart, 1, rowsnum+rowstart-1);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
    public void ExportAge(File file,String start, String end) throws RowsExceededException, WriteException {
		AgesExpData aed = new AgesExpData();
		Map<String, List> zzAges = aed.zzAgesbxData(start,end);
		Map<String, List> txAges = aed.txAgesbxData(start,end);
		Map<String, List> txtj = EUtils.getStateTotaltj("退休",start,end);
		Map<String, List> zztj = EUtils.getStateTotaltj("在职",start,end);
		if(start.contains("01-01")){
			try {
				zztj = EUtils.getStateTotaltjBaogf("在职",start,end);
				txtj = EUtils.getStateTotaltjBaogf("退休",start,end);
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		ExcelStyle es = new ExcelStyle(file,"年龄段统计");
		
		int colstart=2,rowstart=3;
		if(start.contains("01-01")){
			es.setStyleToExcel("AB",start,end); 
		}else 
			es.setStyleToExcel("A",start,end); 
		int rowsnum = es.addExcel(zzAges,colstart,rowstart);
		es.addExcel(zztj,colstart,rowstart+rowsnum);
		es.ws.addCell(new Label(0, rowstart+rowsnum+1, "退休"));
		es.ws.mergeCells(0,rowstart+rowsnum+1, 0, rowsnum+rowstart+5);
		es.addExcel(txAges,colstart,rowstart+rowsnum+1);
		es.addExcel(txtj,colstart,13);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    public void ExportJhsy(File file,String start, String end) throws RowsExceededException, WriteException {
    	OtherData aed = new OtherData();
		//Map<String, List> zzAges = aed.zzAgesbxData();
    	List data = aed.userbxData("计划生育",start,end);
		ExcelStyle es = new ExcelStyle(file,"计划生育统计");
		es.setStyleToExcel("J",start,end); 
        int colstart=1,rowstart=3;
		es.addtyData(data, colstart, rowstart);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    public void ExportDbyw(File file,String start, String end) throws RowsExceededException, WriteException {
    	OtherData aed = new OtherData();
		//Map<String, List> zzAges = aed.zzAgesbxData();
    	List data = aed.userbxData("大病医疗(药物)",start,end);
		ExcelStyle es = new ExcelStyle(file,"大病医疗(药物)统计");
		es.setStyleToExcel("Y",start,end); 
        int colstart=1,rowstart=3;
		es.addtyData(data, colstart, rowstart);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}   
    public void ExportDbqx(File file,String start, String end) throws RowsExceededException, WriteException {
    	OtherData aed = new OtherData();
		//Map<String, List> zzAges = aed.zzAgesbxData();
    	List data = aed.userbxData("大病医疗(器械)",start,end);
		ExcelStyle es = new ExcelStyle(file,"大病医疗(器械)统计");
		es.setStyleToExcel("DQ",start,end); 
        int colstart=1,rowstart=3;
		es.addtyData(data, colstart, rowstart);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    //先放着  还没改
    public void ExportSpecial(File file,String start, String end) throws RowsExceededException, WriteException {
    	OtherData aed = new OtherData();
		//Map<String, List> zzAges = aed.zzAgesbxData();
    	List data = aed.userbxData("大病器械",start,end);
		ExcelStyle es = new ExcelStyle(file,"大病器械统计");
		es.setStyleToExcel("DQ",start,end); 
        int colstart=1,rowstart=3;
		es.addtyData(data, colstart, rowstart);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    
    //normal Users 
    public void ExportUser(File file,String uid,String bxtype,String start,String end) throws RowsExceededException, WriteException {
    	OtherData aed = new OtherData();
    	List wkstates;
    	Map data;
		ExcelStyle es ;
    	if(bxtype.equals("z")){
   		 	 wkstates = aed.getUserWkState(uid, "住院",start,end);
    		 data = aed.singleuserbxData(uid, "住院",start,end,wkstates);
    		 es = new ExcelStyle(file,"住院统计");
    		 es.setStyleToExcel("U",start,end); 
			 es.ws.addCell(new Label(3, 1, "住院"));
			 es.ws.mergeCells(3, 1, 6, 1);
    	}else {
   		 	wkstates = aed.getUserWkState(uid, "门诊",start,end);
    		 data = aed.singleuserbxData(uid, "门诊",start,end,wkstates);
    		 es = new ExcelStyle(file,"门诊统计");
    		 es.setStyleToExcel("U",start,end); 
			 es.ws.addCell(new Label(3, 1, "门诊"));
			 es.ws.mergeCells(3, 1, 6, 1);
    	}
        int colstart=2,rowstart=3;
		int endrow = es.addUsertyData(data, colstart, rowstart);
		try {
			es.wwb.write();
			es.wwb.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

class Byte_File_Object {

	 public static byte[] getBytesFromFile(File f) {
	        if (f == null) {
	            return null;
	        }
	        try {
	            FileInputStream stream = new FileInputStream(f);
	            ByteArrayOutputStream out = new ByteArrayOutputStream(1000);
	            byte[] b = new byte[1000];
	            int n;
	            while ((n = stream.read(b)) != -1)
	                out.write(b, 0, n);
	            stream.close();
	            out.close();
	            return out.toByteArray();
	        } catch (IOException e) {
	        }
	        return null;
	    }

	 
   public static Object getObjectFromBytes(byte[] objBytes) throws Exception {
   if (objBytes == null || objBytes.length == 0) {
       return null;
   }
   ByteArrayInputStream bi = new ByteArrayInputStream(objBytes);
   ObjectInputStream oi = new ObjectInputStream(bi);
   return oi.readObject();
}

 public static byte[] getBytesFromObject(Serializable obj) throws Exception {
   if (obj == null) {
       return null;
   }
   ByteArrayOutputStream bo = new ByteArrayOutputStream();
   ObjectOutputStream oo = new ObjectOutputStream(bo);
   oo.writeObject(obj);
   return bo.toByteArray();
}

}